package cn.edu.sdjzu.xg.kcsj.dao;


import cn.edu.sdjzu.xg.kcsj.domain.Course;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class CourseDao {
    private static CourseDao courseDao = new CourseDao();
    private CourseDao(){}
    public static CourseDao getInstance(){
        return courseDao;
    }


    // SELECT * FROM teacher WHERE name LIKE "%高%" AND no = "yyyy"
    public Collection<Course> findAll (Connection connection, List<Condition> conditionList_json_str, Pagination pagination) throws SQLException {
        Collection<Course>desiredCourses = new HashSet<Course>();
        StringBuilder sql_select  = new StringBuilder ("SELECT * FROM Course");
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause (conditionList_json_str);
            sql_select.append (whereClause); //SELECT * FROM teacher WHERE name LIKE "%高%" AND no = "yyyy"
        }

        int totalNum = CourseDao.getInstance ().count(connection);
        if (pagination != null){
            sql_select.append (pagination.toLimitClause (totalNum)).append (" "); // Limit x1,x2
        }  //将SELECT * FROM teacher WHERE name LIKE "%高%" AND no = "yyyy" Limit x1, x2封装在StringBuilder里面

        //sql_select.toString() 等价于 SELECT * FROM Teacher WHERE name LIKE "%吴%" AND no = "yyyy" LIMIT x1,x2
        PreparedStatement preparedStatement = connection.prepareStatement (sql_select.toString ());
        ResultSet resultSet = preparedStatement.executeQuery ();
        while (resultSet.next ()){
            int id = resultSet.getInt ("id");
            String no = resultSet.getString ("no");
            String name = resultSet.getString ("name");
            Course desiredTeacher = new Course(id,no,name);
            desiredTeacher.setId (id);
            desiredCourses.add (desiredTeacher);
        }
        JdbcHelper.close (resultSet,preparedStatement);
        return desiredCourses;
    }


    public Course find (int id,Connection connection) throws SQLException {
        Course desiredCourse = null;
        PreparedStatement pstmt_select_id = connection.prepareStatement("SELECT * FROM Course" + " WHERE id = ?");
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if(resultSet_select_id.next()){
            String no = resultSet_select_id.getString("no");
            String name = resultSet_select_id.getString("name");
            desiredCourse = new Course (id,no,name);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredCourse;
    }

    private int count (Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement ("SELECT * FROM Course");
        ResultSet resultSet = preparedStatement.executeQuery ();
        int r = 0;
        while (resultSet.next ()){
            r++;
        }
        return r;
    }

    public boolean add (Course course,Connection connection) throws SQLException {
        String add_sql = "INSERT INTO Course(no,name ) VALUES (?,?)";
        PreparedStatement ps = connection.prepareStatement (add_sql);
        ps.setString (1,course.getNo ());
        ps.setString (2,course.getName());
        int add_affectedRowNums = ps.executeUpdate ();
        JdbcHelper.close (ps,connection);
        return add_affectedRowNums > 0;
    }

    public boolean delete (int id,Connection connection) throws SQLException {
        String delect_sql = "DELETE FROM course WHERE id = ?";
        PreparedStatement p = connection.prepareStatement (delect_sql);
        p.setInt (1,id);
        int delete_affectedRowNums = p.executeUpdate ();
        JdbcHelper.close (p,connection);
        return delete_affectedRowNums > 0;
    }

    public boolean update (Course course,Connection connection) throws SQLException {
        String update_sql = "UPDATE course SET no=?, name =? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement (update_sql);
        ps.setString (1,course.getNo ());
        ps.setString (2,course.getName());
        ps.setInt (3,course.getId ());
        int update_affectedRowNums= ps.executeUpdate ();
        return update_affectedRowNums > 0;
    }
}
